﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using DbHelper;//自定义帮助类
using Gx.Model.Entity;//实体
using Gx.Common;//通用类
namespace Gx.Dal.Employee
{
    public partial class G_StockInInfoDal : SqlServerHelper, IDisposable
    {
        public int AddG_StockInInfo(G_StockInInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO G_StockInInfo");
            strSql.Append("(");
            strSql.Append("PurchaseOrderId,");
            strSql.Append("IsPurchaseOrderId,");
            strSql.Append("BillNo,");
            strSql.Append("BillStatus,");
            strSql.Append("ConfirmRemark,");
            strSql.Append("GoodsId,");
            strSql.Append("GoodsName,");
            strSql.Append("EmployeeId,");
            strSql.Append("CheckStatus,");
            strSql.Append("CheckUser,");
            strSql.Append("CheckDate,");
            strSql.Append("CheckUserHistory,");
            strSql.Append("Remark,");
            strSql.Append("Status,");
            strSql.Append("AddUser,");
            strSql.Append("AddTime,");
            strSql.Append("LastEditUser,");
            strSql.Append("LastEditTime");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@PurchaseOrderId,");
            strSql.Append("@IsPurchaseOrderId,");
            strSql.Append("@BillNo,");
            strSql.Append("@BillStatus,");
            strSql.Append("@ConfirmRemark,");
            strSql.Append("@GoodsId,");
            strSql.Append("@GoodsName,");
            strSql.Append("@EmployeeId,");
            strSql.Append("@CheckStatus,");
            strSql.Append("@CheckUser,");
            strSql.Append("@CheckDate,");
            strSql.Append("@CheckUserHistory,");
            strSql.Append("@Remark,");
            strSql.Append("@Status,");
            strSql.Append("@AddUser,");
            strSql.Append("@AddTime,");
            strSql.Append("@LastEditUser,");
            strSql.Append("@LastEditTime");
            strSql.Append(");SELECT @@IDENTITY;");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@PurchaseOrderId", SqlDbType.Int, 4) { Value = model.PurchaseOrderId });
            pList.Add(new SqlParameter("@IsPurchaseOrderId", SqlDbType.Int, 4) { Value = model.IsPurchaseOrderId });
            pList.Add(new SqlParameter("@BillNo", SqlDbType.NVarChar, 30) { Value = model.BillNo });
            pList.Add(new SqlParameter("@BillStatus", SqlDbType.Int, 4) { Value = model.BillStatus });
            pList.Add(new SqlParameter("@ConfirmRemark", SqlDbType.NVarChar, 200) { Value = model.ConfirmRemark });
            pList.Add(new SqlParameter("@GoodsId", SqlDbType.NVarChar, 200) { Value = model.GoodsId });
            pList.Add(new SqlParameter("@GoodsName", SqlDbType.NVarChar, 200) { Value = model.GoodsName });
            pList.Add(new SqlParameter("@EmployeeId", SqlDbType.Int, 4) { Value = model.EmployeeId });
            pList.Add(new SqlParameter("@CheckStatus", SqlDbType.Int, 4) { Value = model.CheckStatus });
            pList.Add(new SqlParameter("@CheckUser", SqlDbType.Int, 4) { Value = model.CheckUser });
            pList.Add(new SqlParameter("@CheckDate", SqlDbType.DateTime) { Value = model.CheckDate });
            pList.Add(new SqlParameter("@CheckUserHistory", SqlDbType.NVarChar, 100) { Value = model.CheckUserHistory });
            pList.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 200) { Value = model.Remark });
            pList.Add(new SqlParameter("@Status", SqlDbType.Int, 4) { Value = model.Status });
            pList.Add(new SqlParameter("@AddUser", SqlDbType.Int, 4) { Value = model.AddUser });
            pList.Add(new SqlParameter("@AddTime", SqlDbType.DateTime) { Value = model.AddTime });
            pList.Add(new SqlParameter("@LastEditUser", SqlDbType.Int, 4) { Value = model.LastEditUser });
            pList.Add(new SqlParameter("@LastEditTime", SqlDbType.DateTime) { Value = model.LastEditTime });
            return ExecuteScalar(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()).IntParse();
        }

        /// <summary>
        /// 作废单据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool DelG_StockInInfo_BillStatus(G_StockInInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE G_StockInInfo SET ");
            strSql.Append("[BillStatus]=@BillStatus,  ");
            strSql.Append("LastEditUser=@LastEditUser, ");
            strSql.Append("LastEditTime=@LastEditTime ");
            strSql.Append("WHERE StockInId=@StockInId  ");
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@StockInId", SqlDbType.Int, 4) { Value = model.StockInId },
                new SqlParameter("@BillStatus", SqlDbType.TinyInt) { Value = model.BillStatus },
                new SqlParameter("@LastEditUser", SqlDbType.Int, 4) { Value = model.LastEditUser },
                new SqlParameter("@LastEditTime", SqlDbType.DateTime) { Value = model.LastEditTime }
            };
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool DelG_StockInInfo(int StockInId)
        {
            string strSql = "DELETE FROM G_StockInInfo WHERE StockInId=@StockInId";
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@StockInId", SqlDbType.Int, 4) { Value = StockInId });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool DelG_StockInInfo(G_StockInInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE G_StockInInfo SET ");
            strSql.Append("[Status]=@Status  ");
            strSql.Append("LastEditUser=@LastEditUser, ");
            strSql.Append("LastEditTime=@LastEditTime ");
            strSql.Append("WHERE StockInId=@StockInId  ");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@StockInId", SqlDbType.Int, 4) { Value = model.StockInId });
            pList.Add(new SqlParameter("@Status", SqlDbType.TinyInt) { Value = model.Status });
            pList.Add(new SqlParameter("@LastEditUser", SqlDbType.Int, 4) { Value = model.LastEditUser });
            pList.Add(new SqlParameter("@LastEditTime", SqlDbType.DateTime) { Value = model.LastEditTime });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool EditG_StockInInfo(G_StockInInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE G_StockInInfo SET ");
            strSql.Append(" PurchaseOrderId=@PurchaseOrderId,");
            strSql.Append(" IsPurchaseOrderId=@IsPurchaseOrderId,");
            strSql.Append(" BillNo=@BillNo,");
            strSql.Append(" BillStatus=@BillStatus,");
            strSql.Append(" ConfirmRemark=@ConfirmRemark,");
            strSql.Append(" GoodsId=@GoodsId,");
            strSql.Append(" GoodsName=@GoodsName,");
            strSql.Append(" EmployeeId=@EmployeeId,");
            strSql.Append(" CheckStatus=@CheckStatus,");
            strSql.Append(" CheckUser=@CheckUser,");
            strSql.Append(" CheckDate=@CheckDate,");
            strSql.Append(" CheckUserHistory=@CheckUserHistory,");
            strSql.Append(" Remark=@Remark,");
            strSql.Append(" Status=@Status,");
            strSql.Append(" AddUser=@AddUser,");
            strSql.Append(" AddTime=@AddTime,");
            strSql.Append(" LastEditUser=@LastEditUser,");
            strSql.Append(" LastEditTime=@LastEditTime");
            strSql.Append(" WHERE StockInId=@StockInId");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@StockInId", SqlDbType.Int, 4) { Value = model.StockInId });
            pList.Add(new SqlParameter("@PurchaseOrderId", SqlDbType.Int, 4) { Value = model.PurchaseOrderId });
            pList.Add(new SqlParameter("@IsPurchaseOrderId", SqlDbType.Int, 4) { Value = model.IsPurchaseOrderId });
            pList.Add(new SqlParameter("@BillNo", SqlDbType.NVarChar, 30) { Value = model.BillNo });
            pList.Add(new SqlParameter("@BillStatus", SqlDbType.Int, 4) { Value = model.BillStatus });
            pList.Add(new SqlParameter("@ConfirmRemark", SqlDbType.NVarChar, 200) { Value = model.ConfirmRemark });
            pList.Add(new SqlParameter("@GoodsId", SqlDbType.NVarChar, 200) { Value = model.GoodsId });
            pList.Add(new SqlParameter("@GoodsName", SqlDbType.NVarChar, 200) { Value = model.GoodsName });
            pList.Add(new SqlParameter("@EmployeeId", SqlDbType.Int, 4) { Value = model.EmployeeId });
            pList.Add(new SqlParameter("@CheckStatus", SqlDbType.Int, 4) { Value = model.CheckStatus });
            pList.Add(new SqlParameter("@CheckUser", SqlDbType.Int, 4) { Value = model.CheckUser });
            pList.Add(new SqlParameter("@CheckDate", SqlDbType.DateTime) { Value = model.CheckDate });
            pList.Add(new SqlParameter("@CheckUserHistory", SqlDbType.NVarChar, 100) { Value = model.CheckUserHistory });
            pList.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 200) { Value = model.Remark });
            pList.Add(new SqlParameter("@Status", SqlDbType.Int, 4) { Value = model.Status });
            pList.Add(new SqlParameter("@AddUser", SqlDbType.Int, 4) { Value = model.AddUser });
            pList.Add(new SqlParameter("@AddTime", SqlDbType.DateTime) { Value = model.AddTime });
            pList.Add(new SqlParameter("@LastEditUser", SqlDbType.Int, 4) { Value = model.LastEditUser });
            pList.Add(new SqlParameter("@LastEditTime", SqlDbType.DateTime) { Value = model.LastEditTime });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        /// <summary>
        /// 审核单据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool EditG_StockInInfo_CheckUser(G_StockInInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE G_StockInInfo SET ");
            strSql.Append("CheckStatus=@CheckStatus,  ");
            strSql.Append("CheckUser=@LastEditUser,  ");
            strSql.Append("LastEditUser=@LastEditUser, ");
            strSql.Append("CheckDate=@LastEditTime, ");
            strSql.Append("LastEditTime=@LastEditTime ");
            strSql.Append("WHERE StockInId=@StockInId  ");
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@StockInId", SqlDbType.Int, 4) { Value = model.StockInId },
                new SqlParameter("@CheckStatus", SqlDbType.Int) { Value = model.CheckStatus },
                new SqlParameter("@LastEditUser", SqlDbType.Int, 4) { Value = model.LastEditUser },
                new SqlParameter("@LastEditTime", SqlDbType.DateTime) { Value = model.LastEditTime }
            };
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }


        public List<G_StockInInfo> GetG_StockInInfoList(string strWhere)
        {
            List<G_StockInInfo> list = new List<G_StockInInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append(" StockInId");
            strSql.Append(",PurchaseOrderId");
            strSql.Append(",IsPurchaseOrderId");
            strSql.Append(",BillNo");
            strSql.Append(",BillStatus");
            strSql.Append(",ConfirmRemark");
            strSql.Append(",GoodsId");
            strSql.Append(",GoodsName");
            strSql.Append(",EmployeeId");
            strSql.Append(",CheckStatus");
            strSql.Append(",CheckUser");
            strSql.Append(",CheckDate");
            strSql.Append(",CheckUserHistory");
            strSql.Append(",Remark");
            strSql.Append(",Status");
            strSql.Append(",AddUser");
            strSql.Append(",AddTime");
            strSql.Append(",LastEditUser");
            strSql.Append(",LastEditTime");
            strSql.AppendFormat(" FROM G_StockInInfo WITH(NOLOCK) WHERE [Status]=0 {0}", strWhere);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int StockInId = r.GetOrdinal("StockInId");
                int PurchaseOrderId = r.GetOrdinal("PurchaseOrderId");
                int IsPurchaseOrderId = r.GetOrdinal("IsPurchaseOrderId");
                int BillNo = r.GetOrdinal("BillNo");
                int BillStatus = r.GetOrdinal("BillStatus");
                int ConfirmRemark = r.GetOrdinal("ConfirmRemark");
                int GoodsId = r.GetOrdinal("GoodsId");
                int GoodsName = r.GetOrdinal("GoodsName");
                int EmployeeId = r.GetOrdinal("EmployeeId");
                int CheckStatus = r.GetOrdinal("CheckStatus");
                int CheckUser = r.GetOrdinal("CheckUser");
                int CheckDate = r.GetOrdinal("CheckDate");
                int CheckUserHistory = r.GetOrdinal("CheckUserHistory");
                int Remark = r.GetOrdinal("Remark");
                int Status = r.GetOrdinal("Status");
                int AddUser = r.GetOrdinal("AddUser");
                int AddTime = r.GetOrdinal("AddTime");
                int LastEditUser = r.GetOrdinal("LastEditUser");
                int LastEditTime = r.GetOrdinal("LastEditTime");
                G_StockInInfo model = null;
                while (r.Read())
                {
                    model = new G_StockInInfo();
                    model.StockInId = r.GetInt32(StockInId);
                    model.PurchaseOrderId = r.GetInt32(PurchaseOrderId);
                    model.IsPurchaseOrderId = r.GetInt32(IsPurchaseOrderId);
                    model.BillNo = r.GetString(BillNo);
                    model.BillStatus = r.GetInt32(BillStatus);
                    model.ConfirmRemark = r.GetString(ConfirmRemark);
                    model.GoodsId = r.GetString(GoodsId);
                    model.GoodsName = r.GetString(GoodsName);
                    model.EmployeeId = r.GetInt32(EmployeeId);
                    model.CheckStatus = r.GetInt32(CheckStatus);
                    model.CheckUser = r.GetInt32(CheckUser);
                    model.CheckDate = r.GetDateTime(CheckDate);
                    model.CheckUserHistory = r.GetString(CheckUserHistory);
                    model.Remark = r.GetString(Remark);
                    model.Status = r.GetInt32(Status);
                    model.AddUser = r.GetInt32(AddUser);
                    model.AddTime = r.GetDateTime(AddTime);
                    model.LastEditUser = r.GetInt32(LastEditUser);
                    model.LastEditTime = r.GetDateTime(LastEditTime);
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        public List<G_StockInInfo> GetG_StockInInfoList(string strWhere, int pageIndex, int pageSize, out int totalCount)
        {
            List<G_StockInInfo> list = new List<G_StockInInfo>();
            totalCount = GetG_StockInInfoCount(strWhere);
            if (totalCount == 0)
            {
                return list;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (");
            strSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY StockInId DESC) AS ORDERBYID");
            strSql.Append(",StockInId");
            strSql.Append(",PurchaseOrderId");
            strSql.Append(",IsPurchaseOrderId");
            strSql.Append(",BillNo");
            strSql.Append(",BillStatus");
            strSql.Append(",ConfirmRemark");
            strSql.Append(",GoodsId");
            strSql.Append(",GoodsName");
            strSql.Append(",EmployeeId");
            strSql.Append(",CheckStatus");
            strSql.Append(",CheckUser");
            strSql.Append(",CheckDate");
            strSql.Append(",CheckUserHistory");
            strSql.Append(",Remark");
            strSql.Append(",Status");
            strSql.Append(",AddUser");
            strSql.Append(",AddTime");
            strSql.Append(",LastEditUser");
            strSql.Append(",LastEditTime");
            strSql.AppendFormat(" FROM G_StockInInfo WITH(NOLOCK) WHERE [Status]=0 {0}) AS T WHERE T.ORDERBYID  BETWEEN {1} AND {2}", strWhere, pageIndex, pageSize);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int StockInId = r.GetOrdinal("StockInId");
                int PurchaseOrderId = r.GetOrdinal("PurchaseOrderId");
                int IsPurchaseOrderId = r.GetOrdinal("IsPurchaseOrderId");
                int BillNo = r.GetOrdinal("BillNo");
                int BillStatus = r.GetOrdinal("BillStatus");
                int ConfirmRemark = r.GetOrdinal("ConfirmRemark");
                int GoodsId = r.GetOrdinal("GoodsId");
                int GoodsName = r.GetOrdinal("GoodsName");
                int EmployeeId = r.GetOrdinal("EmployeeId");
                int CheckStatus = r.GetOrdinal("CheckStatus");
                int CheckUser = r.GetOrdinal("CheckUser");
                int CheckDate = r.GetOrdinal("CheckDate");
                int CheckUserHistory = r.GetOrdinal("CheckUserHistory");
                int Remark = r.GetOrdinal("Remark");
                int Status = r.GetOrdinal("Status");
                int AddUser = r.GetOrdinal("AddUser");
                int AddTime = r.GetOrdinal("AddTime");
                int LastEditUser = r.GetOrdinal("LastEditUser");
                int LastEditTime = r.GetOrdinal("LastEditTime");
                G_StockInInfo model = null;
                while (r.Read())
                {
                    model = new G_StockInInfo();
                    model.StockInId = r.GetInt32(StockInId);
                    model.PurchaseOrderId = r.GetInt32(PurchaseOrderId);
                    model.IsPurchaseOrderId = r.GetInt32(IsPurchaseOrderId);
                    model.BillNo = r.GetString(BillNo);
                    model.BillStatus = r.GetInt32(BillStatus);
                    model.ConfirmRemark = r.GetString(ConfirmRemark);
                    model.GoodsId = r.GetString(GoodsId);
                    model.GoodsName = r.GetString(GoodsName);
                    model.EmployeeId = r.GetInt32(EmployeeId);
                    model.CheckStatus = r.GetInt32(CheckStatus);
                    model.CheckUser = r.GetInt32(CheckUser);
                    model.CheckDate = r.GetDateTime(CheckDate);
                    model.CheckUserHistory = r.GetString(CheckUserHistory);
                    model.Remark = r.GetString(Remark);
                    model.Status = r.GetInt32(Status);
                    model.AddUser = r.GetInt32(AddUser);
                    model.AddTime = r.GetDateTime(AddTime);
                    model.LastEditUser = r.GetInt32(LastEditUser);
                    model.LastEditTime = r.GetDateTime(LastEditTime);
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        public int GetG_StockInInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM G_StockInInfo WITH(NOLOCK) WHERE [Status]=0 {0}", strWhere);
            return int.Parse(ExecuteScalar(ConfigHelper.ConnectionStrings, strSql.ToString()).ToString());
        }

        public G_StockInInfo GetG_StockInInfoModel(int stockInId)
        {
            G_StockInInfo model = new G_StockInInfo();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("StockInId");
            strSql.Append(",PurchaseOrderId");
            strSql.Append(",IsPurchaseOrderId");
            strSql.Append(",BillNo");
            strSql.Append(",BillStatus");
            strSql.Append(",ConfirmRemark");
            strSql.Append(",GoodsId");
            strSql.Append(",GoodsName");
            strSql.Append(",EmployeeId");
            strSql.Append(",CheckStatus");
            strSql.Append(",CheckUser");
            strSql.Append(",CheckDate");
            strSql.Append(",CheckUserHistory");
            strSql.Append(",Remark");
            strSql.Append(",Status");
            strSql.Append(",AddUser");
            strSql.Append(",AddTime");
            strSql.Append(",LastEditUser");
            strSql.Append(",LastEditTime");
            strSql.Append(" FROM G_StockInInfo WITH(NOLOCK) WHERE StockInId=@StockInId");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@StockInId", SqlDbType.Int, 4) { Value = stockInId });
            using (SqlDataReader r = this.ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()))
            {
                int StockInId = r.GetOrdinal("StockInId");
                int PurchaseOrderId = r.GetOrdinal("PurchaseOrderId");
                int IsPurchaseOrderId = r.GetOrdinal("IsPurchaseOrderId");
                int BillNo = r.GetOrdinal("BillNo");
                int BillStatus = r.GetOrdinal("BillStatus");
                int ConfirmRemark = r.GetOrdinal("ConfirmRemark");
                int GoodsId = r.GetOrdinal("GoodsId");
                int GoodsName = r.GetOrdinal("GoodsName");
                int EmployeeId = r.GetOrdinal("EmployeeId");
                int CheckStatus = r.GetOrdinal("CheckStatus");
                int CheckUser = r.GetOrdinal("CheckUser");
                int CheckDate = r.GetOrdinal("CheckDate");
                int CheckUserHistory = r.GetOrdinal("CheckUserHistory");
                int Remark = r.GetOrdinal("Remark");
                int Status = r.GetOrdinal("Status");
                int AddUser = r.GetOrdinal("AddUser");
                int AddTime = r.GetOrdinal("AddTime");
                int LastEditUser = r.GetOrdinal("LastEditUser");
                int LastEditTime = r.GetOrdinal("LastEditTime");
                if (r.Read())
                {
                    model.StockInId = r.GetInt32(StockInId);
                    model.PurchaseOrderId = r.GetInt32(PurchaseOrderId);
                    model.IsPurchaseOrderId = r.GetInt32(IsPurchaseOrderId);
                    model.BillNo = r.GetString(BillNo);
                    model.BillStatus = r.GetInt32(BillStatus);
                    model.ConfirmRemark = r.GetString(ConfirmRemark);
                    model.GoodsId = r.GetString(GoodsId);
                    model.GoodsName = r.GetString(GoodsName);
                    model.EmployeeId = r.GetInt32(EmployeeId);
                    model.CheckStatus = r.GetInt32(CheckStatus);
                    model.CheckUser = r.GetInt32(CheckUser);
                    model.CheckDate = r.GetDateTime(CheckDate);
                    model.CheckUserHistory = r.GetString(CheckUserHistory);
                    model.Remark = r.GetString(Remark);
                    model.Status = r.GetInt32(Status);
                    model.AddUser = r.GetInt32(AddUser);
                    model.AddTime = r.GetDateTime(AddTime);
                    model.LastEditUser = r.GetInt32(LastEditUser);
                    model.LastEditTime = r.GetDateTime(LastEditTime);
                }
                r.Close();
            }
            return model;
        }
    }
}
